<?php
class Model_mailer_task    extends Model {
	function Model_mailer_task() {
		parent::Model();
	}

	function bringTask($table, $s_limit, $e_limit, $iMailerId) {

		$this->db->flush_cache();
		$this->db->limit($e_limit, $s_limit);
		$this->db->where("mailer_id", $iMailerId);
		$this->db->order_by("mailer_task_date", "desc");
		$result = $this->db->get($table)->result_array();
		$totalCount = $this->db->count_all_results();

		foreach ($result as $key => $aTask) {
			$aTaskStatistic = $this->getTaskStatistic($aTask["mailer_task_id"]);
			$result[$key] = array_merge($aTaskStatistic, $aTask);
		}


		$data = array(
			'totalCount' => $totalCount,
			'DS_TASK' => $result
		);
		return $data;
	}

	function getTaskStatistic($iTask) {
		$aResult = array();

		//se scot datele despre task
		$this->db->where("mailer_task_id", $iTask);
		$this->db->join("mailer", "mailer.mailer_id=mailer_task.mailer_id", "left");
		$aTask = $this->db->get("mailer_task")->row_array();


		//calcul total - contacte interne
		$sSql = "		
		SELECT 
			SUM(IF(persons.email <>  '' , 1, 0)) AS email1 ,	SUM(IF(persons.email2 <> '' , 1, 0)) AS email2 ,	COUNT(persons.person_id) as total,
			SUM(IF(persons.email <> '' , 1 , IF(persons.email2 <> '', 1, 0))) as emailC,
			SUM(IF(persons.email2 <> '' , 1 , IF(persons.email <> '', 1, 0))) as emailC2
			FROM client_persons
			LEFT JOIN persons using(person_id) 
			WHERE client_persons.client_id = {$aTask['client_id']}	AND client_persons.extern = 0";
		$aStatIntern = $this->db->query($sSql)->row_array();

		//calcul total - contacte externe
		$sSql = "		
		SELECT 
			SUM(IF(client_persons.email <>  '' , 1, 0)) AS email1 ,	SUM(IF(client_persons.email2 <> '' , 1, 0)) AS email2 ,	COUNT(client_persons.person_id) as total,
			SUM(IF(client_persons.email <> '' , 1 , IF(client_persons.email2 <> '', 1, 0))) as emailC,
			SUM(IF(client_persons.email2 <> '' , 1 , IF(client_persons.email <> '', 1, 0))) as emailC2
			FROM client_persons
			LEFT JOIN persons using(person_id) 
			WHERE client_persons.client_id = {$aTask['client_id']}	AND client_persons.extern = 1";
		$aStatExtern = $this->db->query($sSql)->row_array();


		//calcul cate emailuri s-au trimis
		$sSql = "		
		SELECT 
			SUM(IF(status = 100  , 1, 0)) AS email1_trimis_succes ,	
			SUM(IF(status2 = 100  , 1, 0)) AS email2_trimis_succes ,	
			SUM(IF(status <> 100  , 1, 0)) AS email1_netrimis ,	
			SUM(IF(status2 <> 100 , 1, 0)) AS email2_netrimis 
			FROM mailer_report		
			WHERE mailer_task_id = {$iTask}	";
		$aStatTrimitereEmail = $this->db->query($sSql)->row_array();



		/*
		 Un mailer poate fi de mai multe tipuri
		 ["1","Doar email 1"],
		 ["2","Doar  email 2"],
		 ["3","Ambele"],
		 ["4","Email 1 ( daca e nul atunci email 2)"],
		 ["5","Email 2 ( daca e nul atunci email 1)"]
		 */

		switch ($aTask["mail_type"]) {
			case 1 :
				$aResult = array(
					"total_contacte" => $aStatIntern["total"] + $aStatExtern["total"],
					"total_email1" => $aStatIntern["email1"] + $aStatExtern["email1"],
					"total_email2" => 0,
					"total_de_trimis" => $aStatIntern["email1"] + $aStatExtern["email1"]
				);
				break;
			case 2 :
				$aResult = array(
					"total_contacte" => $aStatIntern["total"] + $aStatExtern["total"],
					"total_email1" => 0,
					"total_email2" => $aStatIntern["email2"] + $aStatExtern["email2"],
					"total_de_trimis" => $aStatIntern["email2"] + $aStatExtern["email2"]
				);
				break;
			case 3 :
				$aResult = array(
					"total_contacte" => $aStatIntern["total"] + $aStatExtern["total"],
					"total_email1" => $aStatIntern["email1"] + $aStatExtern["email1"],
					"total_email2" => $aStatIntern["email2"] + $aStatExtern["email2"],
					"total_de_trimis" => $aStatIntern["email2"] + $aStatExtern["email2"] + $aStatIntern["email1"] + $aStatExtern["email1"]
				);
				break;
			case 4 :
				$aResult = array(
					"total_contacte" => $aStatIntern["total"] + $aStatExtern["total"],
					"total_email1" => $aStatIntern["emailC"] + $aStatExtern["emailC"],
					"total_email2" => 0,
					"total_de_trimis" => $aStatIntern["emailC"] + $aStatExtern["emailC"],
				);
				break;
			case 5 :
				$aResult = array(
					"total_contacte" => $aStatIntern["total"] + $aStatExtern["total"],
					"total_email1" => 0,
					"total_email2" => $aStatIntern["emailC2"] + $aStatExtern["emailC2"],
					"total_de_trimis" => $aStatIntern["emailC2"] + $aStatExtern["emailC2"],
				);
				break;
		}

		$aResult["email1_trimis_succes"] = $aStatTrimitereEmail["email1_trimis_succes"];
		$aResult["email2_trimis_succes"] = $aStatTrimitereEmail["email2_trimis_succes"];
		$aResult["email_trimis_succes"] = $aStatTrimitereEmail["email1_trimis_succes"] + $aStatTrimitereEmail["email2_trimis_succes"];
		$aResult["email1_netrimis"] = $aStatTrimitereEmail["email1_netrimis"];
		$aResult["email2_netrimis"] = $aStatTrimitereEmail["email2_netrimis"];
		$aResult["email_netrimis"] = $aStatTrimitereEmail["email1_netrimis"] + $aStatTrimitereEmail["email2_netrimis"];
		
		$aResult["email_ramas"] =$aResult["total_de_trimis"] - ($aResult["email_trimis_succes"] + $aResult["email_netrimis"]);



		return $aResult;
	}

	/**
	 * Functie de add task ( verificare data)
	 */
	function addTask($data) {

		$this->db->flush_cache();
		$this->db->where("mailer_id", $data["mailer_id"]);
		$this->db->where("lower(mailer_task_date)", $data["mailer_task_date"]);
		$iFound = $this->db->get("mailer_task")->num_rows();

		if ($iFound > 0) {
			return false;
		}
		$this->db->flush_cache();
		$this->db->insert('mailer_task', $data);
		return true;
	}

	function deleteTask($data) {
		$this->db->flush_cache();
		$this->db->where("mailer_task_id", $data['mailer_task_id']);
		$this->db->delete("mailer_task");
	}

	function delayTask($data) {
		if ($data["mailer_task_delay_date"] == "") {
			$data["mailer_task_delay_date"] = null;
		}
		$this->db->flush_cache();
		$this->db->where("mailer_task_id", $data['mailer_task_id']);
		$this->db->update("mailer_task", array("mailer_task_delay_date" => $data["mailer_task_delay_date"]));
	}

	function operationTask($data) {
		$this->db->flush_cache();
		$this->db->where("mailer_task_id", $data['mailer_task_id']);
		$this->db->update("mailer_task", array("mailer_task_status" => $data["mailer_task_status"]));
	}

	function resendTask($data) {
		$this->db->flush_cache();
		$this->db->where_in("status", array(
			1,
			2,
			3,
			11,
			12
		));
		$this->db->where("mailer_task_id", $data['mailer_task_id']);
		$this->db->set("status", 21);
		$this->db->set("descriere", "netrimis");
		$this->db->update("mailer_report");

		$this->db->flush_cache();
		$this->db->where_in("status2", array(
			1,
			2,
			3,
			11,
			12
		));
		$this->db->where("mailer_task_id", $data['mailer_task_id']);
		$this->db->set("status2", 21);
		$this->db->set("descriere2", "netrimis");
		$this->db->update("mailer_report");
	}

	function bringReport($table, $s_limit, $e_limit, $sort, $dir, $iMailerTasksId) {

		$this->db->flush_cache();

		//filter string
		$sWhere = "";
		$aFilters = array(
			"lname" => "mailer_report.lname",
			"fname" => "mailer_report.fname",
			"email" => "mailer_report.email",
			"email2" => "mailer_report.email2"
		);
		foreach ($aFilters as $key => $aFilter) {
			if (isset($_POST[$key]) && ($_POST[$key] != "")) {
				$sValue = $this->db->escape($_POST[$key]);
				$sWhere = " AND $aFilter REGEXP $sValue ";
			}
		}
		
		
		$query = "   
			SELECT SQL_CALC_FOUND_ROWS mailer_report.*, C.name as create_by,  M.name as modify_by, person_id  from mailer_report
				    left join user C on C.user_id = mailer_report.create_by
				    left join user M on M.user_id = mailer_report.modify_by2
				    left join client_persons P on P.client_persons_id = mailer_report.client_persons_id    
   			where mailer_task_id={$iMailerTasksId}  {$sWhere} ORDER BY {$sort} {$dir}  LIMIT {$s_limit},{$e_limit}  ";

		

		//$this->db->limit($e_limit, $s_limit);
		//$this->db->where("mailer_task_id", $iMailerTasksId);
		//$this->db->order_by($sort, $dir);
		$result = $this->db->query($query)->result_array();
		// $totalCount=$this->db->count_all_results();

		$totalCount = $this->db->query('SELECT FOUND_ROWS() total_rows')->row()->total_rows;		

		$data = array(
			'totalCount' => $totalCount,
			'DS_REPORT' => $result
		);
		return $data;
	}

	function editContact($data) {

		$this->db->flush_cache();
		$this->db->where("mailer_report_id", $data["mailer_report_id"]);
		$result = $this->db->get("mailer_report")->row_array();
		/*
		 if($result["status"]==100) {
		 if($data["email"]!=$result["email"]) {
		 return false;
		 }
		 }

		 if($result["status2"]==100) {
		 if($data["email2"]!=$result["email2"]) {
		 return false;
		 }
		 }*/


		$temp = array(
			"email" => $data["email"],
			"email2" => $data["email2"],
			"sex" => $data["sex"],
			"lname" => $data["lname"],
			"fname" => $data["fname"]
		);
		$this->db->flush_cache();
		$this->db->where("mailer_report_id", $data["mailer_report_id"]);
		$this->db->update("mailer_report", $temp);
		return true;
	}

}//end model
